fileType<?php
//查看是否已经入库
include_once __DIR__ . '/BaseShell.php';
ini_set('memory_limit', '1024M');
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;

$expire_time = strtotime("today") - (86400*10);
$expire_exportData = Module_Mediate_ExportData::getInstance()->getAllBySearch(['id','path'],['where'=>["addTime < $expire_time",'exportStatus = 2']]);
foreach ($expire_exportData as $val) {
    $path = LIBRARY_DIR . '/../evidencefiles' . $val['path'];
    if (file_exists($path)) {
        unlink($path); // 删除文件
    }
    Module_Mediate_ExportData::getInstance()->singleUpdate(
        [
            'id' => $val['id'],
            'exportStatus' => 3,
        ]
    );
}



$exportData = Module_Mediate_ExportData::getInstance()->getAllBySearch(['id','mediatorName','fileType','exportType','initialData','exportWhere'],['where'=>['exportStatus = 1']]);

if (empty($exportData)) exit;

foreach ($exportData as $val){
    $exportWhere = $val['exportWhere'] ? json_decode($val['exportWhere'],true) : [];
    $row = 1;
    $start_time = time();
    if($val['exportType'] == 1){
        $cases = Module_Mediate_Case::getInstance()->getAllBySearch(
            ['id','mediationNum','batshId','assetsName','lastCallTime','loanPlatform','litigantName','cardId','customLabel','customGrade','caseStatus','mediateStatus','debtTotal','addTime','entrustExpireTime','mediatorName','contractNo'],
            ['where'=>["id in ({$val['initialData']})"]]);
        if($cases){
            $spreadsheet = new Spreadsheet();
            // 设置xlsx 属性
            $spreadsheet->getProperties()
                ->setCreator('system auto create')
                ->setLastModifiedBy('system auto create')
                ->setTitle('Office 2007 XLSX  Document')
                ->setSubject('Office 2007 XLSX  Document')
                ->setDescription('Test document for Office 2007 XLSX, generated using PHP classes.')
                ->setKeywords('office 2007 openxml php')
                ->setCategory('Test result file');
            // 设置表头
            $spreadsheet->setActiveSheetIndex(0)
                ->setCellValue('A1', '调解案号')
                ->setCellValue('B1', '案件标识号')
                ->setCellValue('C1', '资产方')
                ->setCellValue('D1', '借款平台')
                ->setCellValue('E1', '当事人姓名')
                ->setCellValue('F1', '当事人身份证号码')
                ->setCellValue('G1', '用户标签')
                ->setCellValue('H1', '用户等级')
                ->setCellValue('I1', '案件状态')
                ->setCellValue('J1', '调解状态')
                ->setCellValue('k1', '合同号')
                ->setCellValue('L1', '欠款总额')
                ->setCellValue('M1', '案件入库时间')
                ->setCellValue('N1', '委案到期日期')
                ->setCellValue('O1', '调解员')
                ->setCellValue('P1', '跟进次数')
                ->setCellValue('Q1', '最近通话时间')
                ->setCellValue('R1', '最近通话号码')
                ->setCellValue('S1', '最近沟通结果')
                ->setCellValue('T1', '所有沟通备注');

            foreach ($cases as $case){
                $temp = [];
                $index = $row + 1;
                $temp['mediate_num'] = 0;
                $temp['last_mediate_status'] = '--';
                $temp['last_mediate_desc'] = '';
                $temp['all_mediate_desc'] = '';
                $temp['last_mediate_time'] = date("Y/m/d H:i:s",$case['lastCallTime']);

                $mediate_log = Module_Mediate_CaseMediateLog::getInstance()->getMediateLogByCaseId($case['id'],$exportWhere);
                if($mediate_log){
                    $temp['mediate_num'] = count($mediate_log);
                    $temp['last_mediate_status'] =  $mediate_log[0]['mediateResult'] ? Module_Mediate_Case::$mediateResult[$mediate_log[0]['mediateResult']] : '';
                    foreach ($mediate_log as $log){
                        if(empty($temp['last_mediate_mobile'])){
                            $temp['last_mediate_mobile'] = $log['mobile'];
                        }
                        if($log['desc'] && strpos($log['desc'], '】') !== false){
                            $temp['all_mediate_desc'] .= $log['desc'].';'.PHP_EOL;
                        }else{
                            $temp['all_mediate_desc'] .= date("Y/m/d H:i:s",$log['addTime']) ."-" .$log['desc'].';'.PHP_EOL;
                        }
                    }
                }
                $spreadsheet->setActiveSheetIndex(0)
                    ->setCellValue('A' . $index, $case['mediationNum'])
                    ->setCellValue('B' . $index, $case['batshId'])
                    ->setCellValue('C' . $index, $case['assetsName'])
                    ->setCellValue('D' . $index, $case['loanPlatform'])
                    ->setCellValue('E' . $index, $case['litigantName'])
                    ->setCellValueExplicit('F' . $index, $case['cardId'],'s')
                    ->setCellValue('G' . $index, Module_Mediate_Case::$customLabel[$case['customLabel']])
                    ->setCellValue('H' . $index, Module_Mediate_Case::$customGrade[$case['customGrade']])
                    ->setCellValue('I' . $index, Module_Mediate_Case::$caseStatusArr[$case['caseStatus']])
                    ->setCellValue('J' . $index, Module_Mediate_Case::$mediateStatus[$case['mediateStatus']])
                    ->setCellValueExplicit('K' . $index, $case['contractNo'],'s')
                    ->setCellValue('L' . $index, $case['debtTotal'])
                    ->setCellValue('M' . $index, date("Y/m/d H:i:s",$case['addTime']) )
                    ->setCellValue('N' . $index, date("Y/m/d H:i:s",$case['entrustExpireTime']))
                    ->setCellValue('O' . $index, $case['mediatorName'])
                    ->setCellValue('P' . $index, $temp['mediate_num'])
                    ->setCellValue('Q' . $index, $temp['last_mediate_time'])
                    ->setCellValue('R' . $index, $temp['last_mediate_mobile'])
                    ->setCellValue('S' . $index, $temp['last_mediate_status'])
                    ->setCellValue('T' . $index, $temp['all_mediate_desc']);
                $row ++;

            }
            if(empty($val['fileType'])){
                $file_name = $val['mediatorName'].'-'.date("Y-m-d",time()).'_'.uniqid();
            }else{
                $file_name = $val['fileType'].'_'.uniqid();
            }
            // 重命名表格tab
            $spreadsheet->getActiveSheet()->setTitle('Simple');
            $spreadsheet->setActiveSheetIndex(0);
            $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
            $path = LIBRARY_DIR.'/../evidencefiles/files/exportMediateLogs/';
            if (!file_exists($path)) {
                @mkdir_r($path, 0755);
            }
            $writer->save($path."{$file_name}.xlsx");
            $useTime = time() - $start_time;
            Module_Mediate_ExportData::getInstance()->singleUpdate(
                [
                    'id'=>$val['id'],
                    'exportStatus'=>2,
                    'useTime'=> $useTime ? $useTime : 1 ,
                    'path'=>"/files/exportMediateLogs/{$file_name}.xlsx",
                ]
            );
        }else{
            Module_Mediate_ExportData::getInstance()->singleUpdate(
                [
                    'id'=>$val['id'],
                    'exportStatus'=> 4,
                ]
            );
            continue;
        }

    }
    elseif ($val['exportType'] == 2 ){
        array_push($exportWhere, " talkType = 1");
        $start_time = time();
        $call_record = Module_Mediate_CallRecord::getInstance()->getRecordByCaseIds($val['initialData'],$exportWhere);
        if(empty($call_record)){
            Module_Mediate_ExportData::getInstance()->singleUpdate(
                [
                    'id'=>$val['id'],
                    'exportStatus'=> 4,
                ]
            );
            continue;
        }
        $target_path = LIBRARY_DIR.'/../evidencefiles/files/exportFilesRecord/'."{$val['id']}录音文件/";
        if (!file_exists($target_path)) {
            @mkdir_r($target_path, 0755);
        }
        $fileType = explode('_',$val['fileType']);
        foreach ($call_record as $record){
            $file_name = '';
            foreach ($fileType as $v){
                if($v == 'callTime'){
                    $file_name .= date("YmdHis",$record['callTime']).'_';
                }if($v == 'callTimeDay'){
                     $file_name .= date("Ymd",$record['callTime']).'_';
                }if($v == 'callTimeHour'){
                     $file_name .= date("His",$record['callTime']).'_';
                }if($v == 'callEndTimeHour'){
                    $file_name .= date("His",$record['callEndTime']).'_';
                }elseif($v == 'callType'){
                    $temp = $record['callType'] == 1 ? "呼出" : "呼入";
                    $file_name .= $temp.'_';
                }else{
                    $file_name .= $record[$v].'_';
                }
            }
            $file_name = date('Y-m-d_H-i-s',$record['callTime']).'_'.trim($file_name,'_');
            $file_path = LIBRARY_DIR.'/../evidencefiles/callrecord/'.$record['fileName'];
            if (file_exists($file_path)) {
                @copy($file_path, $target_path."/{$file_name}.mp3");
            }
        }
        zipFolder(LIBRARY_DIR.'/../evidencefiles/files/exportFilesRecord/'."{$val['id']}录音文件", LIBRARY_DIR.'/../evidencefiles/files/exportFilesRecord/'."{$val['id']}录音文件.zip");
        deleteFolder(LIBRARY_DIR.'/../evidencefiles/files/exportFilesRecord/'."{$val['id']}录音文件");
        $useTime = time() - $start_time;
        Module_Mediate_ExportData::getInstance()->singleUpdate(
            [
                'id'=>$val['id'],
                'exportStatus'=>2,
                'useTime'=> $useTime ? $useTime : 1 ,
                'path'=>'/files/exportFilesRecord/'."{$val['id']}录音文件.zip",
            ]
        );


    }
    elseif ($val['exportType'] == 3 ){
        $start_time = time();
        $call_record = Module_Mediate_CallRecord::getInstance()->getRecordByCaseIds($val['initialData'],$exportWhere);
        if(empty($call_record)){
            Module_Mediate_ExportData::getInstance()->singleUpdate(
                [
                    'id'=>$val['id'],
                    'exportStatus'=> 4,
                ]
            );
            continue;
        }
        $spreadsheet = new Spreadsheet();
        // 设置xlsx 属性
        $spreadsheet->getProperties()
            ->setCreator('system auto create')
            ->setLastModifiedBy('system auto create')
            ->setTitle('Office 2007 XLSX  Document')
            ->setSubject('Office 2007 XLSX  Document')
            ->setDescription('Test document for Office 2007 XLSX, generated using PHP classes.')
            ->setKeywords('office 2007 openxml php')
            ->setCategory('Test result file');
        // 设置表头
        $spreadsheet->setActiveSheetIndex(0)
            ->setCellValue('A1', '调解案号')
            ->setCellValue('B1', '案件标识号')
            ->setCellValue('C1', '资产方')
            ->setCellValue('D1', '当事人')
            ->setCellValue('E1', '合同号')
            ->setCellValue('F1', '导入批次id')
            ->setCellValue('G1', '通话号码')
            ->setCellValue('H1', '通话类型')
            ->setCellValue('I1', '接通状态')
            ->setCellValue('J1', '呼叫时间')
            ->setCellValue('K1', '结束时间')
            ->setCellValue('L1', '通话时长')
            ->setCellValue('M1', '通话调解员')
            ->setCellValue('N1', '案件号');

        if($call_record) {
            foreach ($call_record as $record) {
                $temp = [];
                $index = $row + 1;

                $spreadsheet->setActiveSheetIndex(0)
                    ->setCellValue('A' . $index, $record['mediationNum'])
                    ->setCellValue('B' . $index, $record['batshId'])
                    ->setCellValue('C' . $index, $record['assetsName'])
                    ->setCellValue('D' . $index, $record['litigantName'])
                    ->setCellValueExplicit('E' . $index, $record['contractNo'],'s')
                    ->setCellValue('F' . $index, $record['importRecordId'])
                    ->setCellValue('G' . $index, $record['callPhone'])
                    ->setCellValue('H' . $index, $record['callType'] == 1 ? '呼出' : '呼入')
                    ->setCellValue('I' . $index, $record['talkType'] == 1 ? '接通' : '未接通')
                    ->setCellValue('J' . $index, date("Y/m/d H:i:s", $record['callTime']))
                    ->setCellValue('K' . $index, date("Y/m/d H:i:s", $record['callEndTime']))
                    ->setCellValue('L' . $index, $record['talkTime'])
                    ->setCellValue('M' . $index, $record['mediatorName'])
                    ->setCellValue('N' . $index, $record['caseId']);
                $row++;

            }
        }
        if(empty($val['fileType'])){
            $file_name = $val['mediatorName'].'-'.date("Y-m-d",time()).'_'.uniqid();
        }else{
            $file_name = $val['fileType'].'_'.uniqid();
        }
        // 重命名表格tab
        $spreadsheet->getActiveSheet()->setTitle('Simple');
        $spreadsheet->setActiveSheetIndex(0);
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $path = LIBRARY_DIR.'/../evidencefiles/files/exportMediateLogs/';
        if (!file_exists($path)) {
            @mkdir_r($path, 0755);
        }
        $writer->save($path."{$file_name}.xlsx");
        $useTime = time() - $start_time;
        Module_Mediate_ExportData::getInstance()->singleUpdate(
            [
                'id'=>$val['id'],
                'exportStatus'=>2,
                'useTime'=> $useTime ? $useTime : 1 ,
                'path'=>"/files/exportMediateLogs/{$file_name}.xlsx",
            ]
        );


    }
    elseif ($val['exportType'] == 4 ){
        $cases = Module_Mediate_Case::getInstance()->getAllBySearch(
            ['id','mediationNum','batshId','assetsName','lastCallTime','loanPlatform','litigantName','cardId','customLabel','customGrade','caseStatus','mediateStatus','debtTotal','addTime','entrustExpireTime','mediatorName','contractNo'],
            ['where'=>["id in ({$val['initialData']})"]]);
        if($cases){
            $spreadsheet = new Spreadsheet();
            // 设置xlsx 属性
            $spreadsheet->getProperties()
                ->setCreator('system auto create')
                ->setLastModifiedBy('system auto create')
                ->setTitle('Office 2007 XLSX  Document')
                ->setSubject('Office 2007 XLSX  Document')
                ->setDescription('Test document for Office 2007 XLSX, generated using PHP classes.')
                ->setKeywords('office 2007 openxml php')
                ->setCategory('Test result file');
            // 设置表头
            $spreadsheet->setActiveSheetIndex(0)
                ->setCellValue('A1', '调解案号')
                ->setCellValue('B1', '案件标识号')
                ->setCellValue('C1', '资产方')
                ->setCellValue('D1', '借款平台')
                ->setCellValue('E1', '当事人姓名')
                ->setCellValue('F1', '当事人身份证号码')
                ->setCellValue('G1', '用户标签')
                ->setCellValue('H1', '用户等级')
                ->setCellValue('I1', '案件状态')
                ->setCellValue('J1', '调解状态')
                ->setCellValue('k1', '合同号')
                ->setCellValue('L1', '欠款总额')
                ->setCellValue('M1', '案件入库时间')
                ->setCellValue('N1', '委案到期日期')
                ->setCellValue('O1', '调解员')
                ->setCellValue('P1', '发送次数')
                ->setCellValue('Q1', '所有内容');

            foreach ($cases as $case){
                $temp = [];
                $index = $row + 1;
                $temp['sms_num'] = 0;
                $temp['all_sms'] = '';
                $where = $exportWhere;
                array_push($where,"caseId = {$case['id']}");
                array_push($where,"sendStatus in (1,2,4,5)");
                $sms_log = Module_Mediate_SmsRecord::getInstance()->getAllBySearch(['sendTime','sendStatus','sign','content'],['where'=>$where]);
                if($sms_log){
                    $temp['sms_num'] = count($sms_log);
                    foreach ($sms_log as $log){
                        $sendStatus = $log['sendStatus'] == 4 ? '失败' : '成功';
                        $temp['all_sms'] .= date("Y/m/d H:i:s",$log['sendTime']) ."-". $sendStatus ."：".$log['sign'].$log['content'].';'.PHP_EOL;
                    }
                }
                $spreadsheet->setActiveSheetIndex(0)
                    ->setCellValue('A' . $index, $case['mediationNum'])
                    ->setCellValue('B' . $index, $case['batshId'])
                    ->setCellValue('C' . $index, $case['assetsName'])
                    ->setCellValue('D' . $index, $case['loanPlatform'])
                    ->setCellValue('E' . $index, $case['litigantName'])
                    ->setCellValueExplicit('F' . $index, $case['cardId'],'s')
                    ->setCellValue('G' . $index, Module_Mediate_Case::$customLabel[$case['customLabel']])
                    ->setCellValue('H' . $index, Module_Mediate_Case::$customGrade[$case['customGrade']])
                    ->setCellValue('I' . $index, Module_Mediate_Case::$caseStatusArr[$case['caseStatus']])
                    ->setCellValue('J' . $index, Module_Mediate_Case::$mediateStatus[$case['mediateStatus']])
                    ->setCellValueExplicit('K' . $index, $case['contractNo'],'s')
                    ->setCellValue('L' . $index, $case['debtTotal'])
                    ->setCellValue('M' . $index, date("Y/m/d H:i:s",$case['addTime']) )
                    ->setCellValue('N' . $index, date("Y/m/d H:i:s",$case['entrustExpireTime']))
                    ->setCellValue('O' . $index, $case['mediatorName'])
                    ->setCellValue('P' . $index, $temp['sms_num'])
                    ->setCellValue('Q' . $index, $temp['all_sms']);
                $row ++;

            }
            if(empty($val['fileType'])){
                $file_name = $val['mediatorName'].'-'.date("Y-m-d",time()).'_'.uniqid();
            }else{
                $file_name = $val['fileType'].'_'.uniqid();
            }
            // 重命名表格tab
            $spreadsheet->getActiveSheet()->setTitle('Simple');
            $spreadsheet->setActiveSheetIndex(0);
            $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
            $path = LIBRARY_DIR.'/../evidencefiles/files/exportSmsLogs/';
            if (!file_exists($path)) {
                @mkdir_r($path, 0755);
            }
            $writer->save($path."{$file_name}.xlsx");
            $useTime = time() - $start_time;
            Module_Mediate_ExportData::getInstance()->singleUpdate(
                [
                    'id'=>$val['id'],
                    'exportStatus'=>2,
                    'useTime'=> $useTime ? $useTime : 1 ,
                    'path'=>"/files/exportSmsLogs/{$file_name}.xlsx",
                ]
            );
        }else{
            Module_Mediate_ExportData::getInstance()->singleUpdate(
                [
                    'id'=>$val['id'],
                    'exportStatus'=> 4,
                ]
            );
            continue;
        }

    }
}


function mkdir_r($dirName, $rights=0755){
    $dirs = explode('/', $dirName);
    $dir='';
    foreach ($dirs as $part) {
        $dir.=$part.'/';
        if (!is_dir($dir) && strlen($dir)>0)
            mkdir($dir, $rights);
    }
}
function zipFolder($source, $destination) {
    if (!extension_loaded('zip') || !file_exists($source)) {
        return false;
    }

    $zip = new ZipArchive();
    if (!$zip->open($destination, ZipArchive::CREATE)) {
        return false;
    }

    $source = str_replace('\\', '/', realpath($source));

    if (is_dir($source) === true) {
        $files = new RecursiveIteratorIterator(new RecursiveDirectoryIterator($source), RecursiveIteratorIterator::SELF_FIRST);

        foreach ($files as $file) {
            $file = str_replace('\\', '/', $file);

            // Ignore "." and ".." folders
            if (in_array(substr($file, strrpos($file, '/')+1), array('.', '..')))
                continue;

            $file = realpath($file);

            if (is_dir($file) === true) {
                $zip->addEmptyDir(str_replace($source . '/', '', $file . '/'));
            } else if (is_file($file) === true) {
                $zip->addFromString(str_replace($source . '/', '', $file), file_get_contents($file));
            }
        }
    } else if (is_file($source) === true) {
        $zip->addFromString(basename($source), file_get_contents($source));
    }

    return $zip->close();
}

function deleteFolder($folderPath) {
    if (is_dir($folderPath)) {
        if (is_dir_empty($folderPath)) {
            rmdir($folderPath);
        } else {
            // 文件夹不为空，先删除其中所有文件和文件夹
            $files = scandir($folderPath);
            foreach ($files as $file) {
                if ($file != "." && $file != "..") {
                    $filePath = $folderPath . "/" . $file;
                    if (is_dir($filePath)) {
                        deleteFolder($filePath);
                    } else {
                        unlink($filePath);
                    }
                }
            }
            // 删除完子文件和子文件夹后再删除空文件夹
            rmdir($folderPath);
        }
    }
}

function is_dir_empty($folderPath) {
    $files = scandir($folderPath);
    return count($files) <= 2;
}
